ALTER PROCEDURE [dbo].[spe_smsfetch_ucis_sfslbd]
as
begin
	declare @t_id int
	declare @t_yhbh varchar(20)		--用户编号
	declare @t_ysdz varchar(200)		--用水地址	  
	declare	@t_sjhm varchar(30)		--手机号码
	declare @t_lyear varchar(4),@t_lmonth varchar(4),@t_lday varchar(4)
	declare @t_year varchar(4),@t_month varchar(4),@t_day varchar(4)
	declare @t_smsinfo varchar(2000),@t_smsguid varchar(36)
	declare @sms_guid varchar(36),@t_Lscsj datetime,@t_Cscsj datetime
	declare @g_status int,@t_bysf decimal(12,2),@t_bypsf decimal(12,2),@t_byljf decimal(12,2)
	declare @doc_count int,@IsZNJ int
	declare @cj_count int
	declare @g_nf	int	
	declare @g_yf	int
	declare @t_itemid bigint
	declare @t_deptcode varchar(10)
	declare @t_INTERVAL_LENGTH int
	declare @t_amount int
	declare @i_smsnum int
	declare @c_batch varchar(2)
	declare @t_sfznj decimal(12,2),@t_psfznj decimal(12,2),@t_ljfznj decimal(12,2)
	declare @t_sfCostYearMonthStr varchar(300),@t_psfCostYearMonthStr varchar(300),@t_ljfCostYearMonthStr varchar(300),@t_RecordStr varchar(1000)
	declare @t_wyj decimal(12,2),@t_modeltype varchar(20),@flag_num int
	declare @plan_status int
	declare @t_sccbrq VARCHAR(36)	
	declare @t_expectedstartdate datetime	
	declare @t_cbrq VARCHAR(36)		--抄表日期
	declare @t_ysl int				--用水量
	declare @t_lysl int				--用水量
	declare @t_bdlv decimal(16,4)	--波动率
	declare @t_bdlv1 decimal(9,2)
	DECLARE @tguid VARCHAR(36)
	DECLARE @sname VARCHAR(255)
	DECLARE @sms_content varchar(2000)
	DECLARE @sm_phone varchar(20)
	DECLARE @deptcode varchar(20)
  DECLARE @sms_content_time datetime
	--取期望发送时间
	select @g_status=COUNT(*) from ucis_sms_grab where type_id='A00013'  and status1 is null
	PRINT @g_status	
	if @g_status>0
	begin
		select top 1 @sms_guid=sms_guid from ucis_sms_grab where TYPE_ID='A00013'  and status1 is null
		select @plan_status=STATUS from ucis_smssendplan WHERE rec_guid=@sms_guid
		PRINT '短信计划GUID:' + ISNULL(@sms_guid, '/')
		
		if @sms_guid!='' and  @plan_status=0
		begin
			update ucis_sms_monitoring set doc_starttime=GETDATE(),cj_starttime=GETDATE() where SMS_GUID=@sms_guid
			select @c_batch=BATCH from ucis_smssendplan where type_id='A00013' and REC_GUID=@sms_guid
	
			set @i_smsnum=0
			
			declare cur_sjhm cursor for select yhbh,Convert(Varchar(100), sccbrq, 23) as sccbrq,Convert(Varchar(100), cbrq, 23) as cbrq,ysl,bdlv,sjhm,ysdz,id,nf,yf,itemid,deptcode,lysl from ucis_sf_slbd 
			where len(sjhm)=11 and SMS_GUID=@sms_guid
			--处理发送记录
			open cur_sjhm
			fetch next from cur_sjhm into @t_yhbh,@t_sccbrq,@t_cbrq,@t_ysl,@t_bdlv,@t_sjhm,@t_ysdz,@t_id,@g_nf,@g_yf,@t_itemid,@t_deptcode,@t_lysl
			while @@fetch_status=0
			begin
				begin transaction
			
--				print(@t_yhbh)
				begin
				/*--模板选取
					if (@IsZNJ=1)
					begin
						set @t_modeltype='1'
					end
					else
					begin
						set @t_modeltype='2'
					end
					if @t_sfCostYearMonthStr!=''
					begin
						set @t_modeltype=@t_modeltype+'1'
					end
					else
					begin
						set @t_modeltype=@t_modeltype+'2'
					end
					if @t_psfCostYearMonthStr!=''
					begin
						set @t_modeltype=@t_modeltype+'1'
					end
					else
					begin
						set @t_modeltype=@t_modeltype+'2'
					end
					if @t_ljfCostYearMonthStr!=''
					begin
						set @t_modeltype=@t_modeltype+'1'
					end
					else
					begin
						set @t_modeltype=@t_modeltype+'2'
					end
					set @t_modeltype=@t_modeltype+'00'
					select @t_smsinfo=content from smsmodel where ID=@t_modeltype and type='A00003'*/
					
					/* 2022-11-21模板调整备份
					--短信模板生成
					set @t_lyear=year(@t_sccbrq)
					set @t_lmonth=month(@t_sccbrq)
					set @t_lday=day(@t_sccbrq)
					set @t_year=year(@t_cbrq)
					set @t_month=month(@t_cbrq)
					set @t_day=day(@t_cbrq)				   
				
					--set @t_bdlv1=@t_bdlv*100
					select @t_smsinfo=content from smsmodel where ID='BD0007' and type='A00003'
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',cast(@t_yhbh as varchar(12)))
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_lyear as varchar(4)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_lmonth as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_lday as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_month as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_day as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_lysl as varchar(20)))
					set @t_smsguid=newid()
					print(@t_smsinfo)*/
					
					-- 2022-11-21模板调整
					select @t_smsinfo=content from smsmodel where ID='BD0007' and type='A00003'
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',cast(@t_yhbh as varchar(12)))
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'sccbrq',@t_sccbrq)
					set @t_smsinfo=REPLACE(@t_smsinfo,'cbrq',@t_cbrq)
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
					set @t_smsguid=newid()
					print(@t_smsinfo)


					/*--数据拼接
					set @t_wyj=0
					set @t_wyj=@t_sfznj+@t_psfznj
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',@t_RecordStr)
					if @t_sfCostYearMonthStr!=''
					begin
						set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',@t_sfCostYearMonthStr)
						set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_bysf as varchar(20)))
					end
					if @t_psfCostYearMonthStr!=''
					begin
						set @t_smsinfo=REPLACE(@t_smsinfo,'fff',@t_psfCostYearMonthStr)
						set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_bypsf as varchar(20)))
					end
					if @t_ljfCostYearMonthStr!=''
					begin
						set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',@t_ljfCostYearMonthStr)
						set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_byljf as varchar(20)))
					end
					set @t_smsinfo=REPLACE(@t_smsinfo,'jjj',cast(@t_wyj as varchar(20)))*/
				end
			
				--写历史记录表
				begin 
					insert into ucis_sf_slbd_his(rec_guid,nf,yf,yhbh,lysl,sccbrq,cbrq,ysl,bdlv,lxdh,sjhm,ysdz,gss,result,telresult,sendtime,id,itemid,deptcode,doc_guid,sms_guid)
					select newid(),nf,yf,yhbh,lysl,sccbrq,cbrq,ysl,bdlv,lxdh,sjhm,ysdz,gss,result,telresult,sendtime,id,itemid,deptcode,@t_smsguid,sms_guid from ucis_sf_slbd where id=@t_id and deptcode=@t_deptcode and sms_guid=@sms_guid
					if @@error<>0
					begin
						rollback transaction
						goto nextpoint1
					end
				end
				--写入CJ_Comprehensive_UICS表
				begin
					insert into CJ_Comprehensive_UCIS(nf,yf,yhbh,sjhm,doc_guid,sms_comment,type_id,ysdz,id,CJ_GUID,sms_guid,itemid,deptcode)values 
					(@g_nf,@g_yf,@t_yhbh,@t_sjhm,@t_smsguid,@t_smsinfo,'A00013',@t_ysdz,@t_id,NEWID(),@sms_guid,@t_itemid,@t_deptcode)
					if @@error<>0
					begin
						rollback transaction
						goto nextpoint1
					end
				end										
				--写发送表
				if len(@t_sjhm)=11
				begin
					insert into DOCUMENTOUT_UCIS(documentout_guid,doctype,status,documentpath,documentto,createdby,createddate,expectedstartdate,stringfield1,stringfield2,TRIALCOUNT,TRIALCOUNTLIMIT,SMS_GUID,reportstatus)
					values(@t_smsguid,'3','0',@t_smsinfo,@t_sjhm,'SYSTEM',getdate(),@t_expectedstartdate,'A00013',@t_yhbh,0,3,@sms_guid,'FINISH')
					if @@error<>0
					begin
						rollback transaction
						goto nextpoint1
					end
					set @i_smsnum=@i_smsnum+1
				end
				--清除发送记录
				begin
					delete from ucis_sf_slbd where itemid=@t_itemid and deptcode=@t_deptcode and sms_guid=@sms_guid
					if @@error<>0
					begin
						rollback transaction
						goto nextpoint1
					end
				end

				nextpoint1:
				commit transaction
				fetch next from cur_sjhm into @t_yhbh,@t_sccbrq,@t_cbrq,@t_ysl,@t_bdlv,@t_sjhm,@t_ysdz,@t_id,@g_nf,@g_yf,@t_itemid,@t_deptcode,@t_lysl
			end;
			close cur_sjhm
			deallocate cur_sjhm
			
			select @flag_num=COUNT(1) from ucis_sf_slbd where len(sjhm)=11 and SMS_GUID=@sms_guid
			if @flag_num=0
			begin
				--更新生成完成状态
				begin
					update DOCUMENTOUT_UCIS set reportstatus='FINISH' where SMS_GUID=@sms_guid
           update ucis_smssendplan set STATUS=2 where rec_guid=@sms_guid
				end
				begin
					select @doc_count=count(*) from DOCUMENTOUT_UCIS where sms_guid=@sms_guid 
					select @cj_count=COUNT(*) from CJ_Comprehensive_UCIS where sms_guid=@sms_guid
					update ucis_sms_monitoring set doc_sum=@doc_count,cj_sum=@cj_count,doc_endtime=GETDATE(),cj_endtime=GETDATE(),smsfetch_status=1 where SMS_GUID=@sms_guid
				end
				begin
		
					 update ucis_sms_grab set status1='1'  where type_id='A00013' and status1 is null  AND SMS_GUID = @sms_guid
					update CJ_Comprehensive_UCIS set createdate = GETDATE() where sms_guid=@sms_guid
				end	
				BEGIN
					select top 1 @tguid = REC_GUID,@sname = sms_name from ucis_smssendplan where rec_guid = @sms_guid
					INSERT INTO message_operation_his VALUES (@tguid,@sname,'job', GETDATE(), '内容拼接',NEWID());
				END
			end
				--审核通知短信
				begin
					select @sms_content=sms_name+'，短信拼接已完成，时间为'+CONVERT(varchar(30),GETDATE(),120)+'，请及时前往计划页面配置发送时间、分批设置。【深圳水务集团】',@deptcode = deptcode  from ucis_smssendplan 
					where type_id='A00013' and rec_guid=@sms_guid
					if @sms_content!='' or @sms_content is not null
					begin
						declare sms_audits_A00012 cursor for select phone from sms_audits where area = @deptcode
						open sms_audits_A00012
						fetch next from sms_audits_A00012 into @sm_phone
						while @@fetch_status=0
						begin

             Select @sms_content_time = Case
										 When Getdate() Between Convert(Varchar(100), Getdate(), 23) + ' ' + '09:00:00' And
													Convert(Varchar(100), Getdate(), 23) + ' ' + '23:59:59' Then
											Getdate()
										 Else
											Datename(Year, Getdate()) + '-' + Datename(Month, Getdate()) + '-' + Datename(Day, Getdate()) + ' ' +
											'09:00:00'
									 End

							begin
							/*insert into ITSV.szwaterDB.DBO.gxsendlist (content,sendtime,loginname,mobile,typework,bh,status,isbirthday,depid,schtime)
							values(@sms_content,@sms_content_time,'linzm',@sm_phone,'UCIS水量波动','SZWG','0','0',1,@sms_content_time)*/
								Declare @Out_SMS_Guid Varchar(36)
								Exec Proc_Send_SMS_Time @sm_phone, @sms_content, @sms_content_time, @Out_SMS_Guid OUTPUT
								PRINT @Out_SMS_Guid
							end
							fetch next from sms_audits_A00012 into @sm_phone
						end
						close sms_audits_A00012
						deallocate sms_audits_A00012
					end
				end

		end
	end
end;